In [1]:
import pandas as pd
import numpy as np
import warnings
warnings.filterwarnings("ignore")

import upsetplot
from upsetplot import generate_counts, plot, UpSet

from plotly_upset.plotting import plot_upset
from retentioneering.eventstream import Eventstream

Customers Intersection Analysis for Pet Brands¶

  1. Sequence of Registrations in Brands
  2. Sequence of Orders in Brands
  3. Customer Distribution by Number of Brands
  4. Brand Preferences Among Customers
  5. Brand Customer Intersection Scheme
  6. Channels Intersection Scheme
In [2]:
df_clients_1 = pd.read_csv('df_clients_done_01.csv')
df_clients_2 = pd.read_csv('df_clients_done_02.csv')
clients = pd.concat([df_clients_1, df_clients_2])
columns = pd.DataFrame(clients.columns)
In [3]:
registration_dates = clients[['CustomerIdsMindboxId',
                              'CustomerCustomFieldsAnicuraRegistrationDate',
                              'CustomerCustomFieldsKinpetRegistrationDate',
                              'CustomerCustomFieldsLubimchikRegistrationDate',
                              'CustomerCustomFieldsPetstoryRegistrationDate',
                              'CustomerCustomFieldsRoyalcaninRegistrationDate']]

registration_dates.columns=['id', 'Anicura','Kinpet','Lubimchik','Petstory','Royalcanin']

registration_dates = pd.melt(registration_dates, id_vars=['id'], var_name='brand', value_name='timestamp')
registration_dates = registration_dates[registration_dates['timestamp'].notna()]
In [4]:
regs_1 = pd.read_csv('df_reg_done_01.csv')
regs_2 = pd.read_csv('df_reg_done_02.csv')
regs_3 = pd.read_csv('df_reg_done_03.csv')

regs = pd.concat([regs_1,regs_2,regs_3])

actions = regs[['CustomerActionCustomerIdsMindboxId','CustomerActionCreationDateTimeUtc','CustomerActionBrandIdsSystemName','CustomerActionActionTemplateName']]
actions.columns=['id','timestamp','brand','action']
In [5]:
first_action = actions.groupby('id').agg({'timestamp':'min'})
first_action = first_action.reset_index()

first_action = first_action.merge(actions, on=['id','timestamp'])
In [6]:
first_action['timestamp'] = pd.to_datetime(first_action['timestamp']).dt.date
In [7]:
df_orders = pd.read_csv('df_orders_other_brands.csv')
df_orders_royal = pd.read_csv('df_orders_royal_canin.csv')

df_orders_royal = df_orders_royal[['OrderCustomerIdsMindboxId','OrderFirstActionDateTimeUtc']]
df_orders_royal['brand'] = 'RoyalCanin'
df_orders_royal.columns=['id','timestamp','brand']

df_orders = df_orders[['OrderCustomerIdsMindboxId','OrderFirstActionDateTimeUtc','OrderFirstActionChannelIdsExternalId']]

df_orders.columns=['id','timestamp','brand']

df_orders = pd.concat([df_orders,df_orders_royal])

df_orders.loc[df_orders['brand'].str.contains('Lubimchik', na=False), 'brand'] = 'Lubimchik'
df_orders.loc[df_orders['brand'].str.contains('PetStory', na=False), 'brand'] = 'PetStory'
df_orders.loc[df_orders['brand'].str.contains('Kinpet', na=False), 'brand'] = 'Kinpet'
df_orders.loc[df_orders['brand'].str.contains('Anicura', na=False), 'brand'] = 'Anicura'
df_orders.loc[df_orders['brand'].str.contains('Белый клык', na=False), 'brand'] = 'Белый клык'
In [8]:
first_order = df_orders.groupby('id').agg({'timestamp':'min'})
first_order = first_order.reset_index()

first_order = first_order.merge(df_orders, on=['id','timestamp'])
In [9]:
registration_dates = pd.concat([first_action, first_order, registration_dates])
In [10]:
registration_dates['timestamp'] = pd.to_datetime(registration_dates['timestamp'])
In [11]:
registration_dates = registration_dates.replace({'Petstory':'PetStory'})
In [12]:
registration_dates = registration_dates[registration_dates['brand'].str.contains('Lubimchik|Anicura|PetNutrition|Kinpet|RoyalCanin|PetStory',na=False)]
In [13]:
registration_dates = registration_dates[['id','timestamp','brand']].drop_duplicates().sort_values(by=['id','timestamp']).drop_duplicates(subset=['id','brand'], keep='first')
In [14]:
registration_dates.columns=['user_id','timestamp','event']

Sequence of Registrations in Brands ¶

In [16]:
stream = Eventstream(registration_dates)
stream\
    .transition_graph(
    edges_norm_type=None,
    edges_weight_col='user_id'
    )
Out[16]:
<retentioneering.tooling.transition_graph.transition_graph.TransitionGraph at 0x2388354f520>

Sequence of Orders in Brands¶

In [17]:
df_orders.columns=['user_id','timestamp','event']

stream = Eventstream(df_orders)

stream.transition_graph(
    edges_norm_type=None,
    edges_weight_col='user_id'
    )
Out[17]:
<retentioneering.tooling.transition_graph.transition_graph.TransitionGraph at 0x23884bb97c0>
In [18]:
contacts = clients[['CustomerIdsMindboxId','CustomerEmail','CustomerMobilePhone']]
contacts.columns=['id','email','phone']
In [19]:
anicura_clients = clients[['CustomerIdsMindboxId',
                           'CustomerIdsAnicuraID',
                           'CustomerCustomFieldsAnicuraEmail',
                           'CustomerCustomFieldsAnicuraMobilephone',
                           'CustomerCustomFieldsAnicuraRegistrationDate',
                           'CustomerCustomerSubscriptionsAnicuraIsSubscribed',
                           'CustomerCustomerSubscriptionsAnicuraSmsIsSubscribed',
                           'CustomerCustomerSubscriptionsAnicuraEmailIsSubscribed',
                           'CustomerCustomerSubscriptionsAnicuraViberIsSubscribed',
                           'CustomerCustomerSubscriptionsAnicuraMobilePushIsSubscribed',
                           'CustomerCustomerSubscriptionsAnicuraWebPushIsSubscribed']]

anicura_clients = pd.DataFrame(anicura_clients.groupby('CustomerIdsMindboxId').count().sum(axis=1))
anicura_clients = anicura_clients[anicura_clients[0]>0]
In [20]:
anicura_clients = clients[['CustomerIdsMindboxId',
                           'CustomerIdsAnicuraID',
                           'CustomerCustomFieldsAnicuraEmail',
                           'CustomerCustomFieldsAnicuraMobilephone',
                           'CustomerCustomFieldsAnicuraRegistrationDate',
                           'CustomerCustomerSubscriptionsAnicuraIsSubscribed',
                           'CustomerCustomerSubscriptionsAnicuraSmsIsSubscribed',
                           'CustomerCustomerSubscriptionsAnicuraEmailIsSubscribed',
                           'CustomerCustomerSubscriptionsAnicuraViberIsSubscribed',
                           'CustomerCustomerSubscriptionsAnicuraMobilePushIsSubscribed',
                           'CustomerCustomerSubscriptionsAnicuraWebPushIsSubscribed']]

anicura_clients = pd.DataFrame(anicura_clients.groupby('CustomerIdsMindboxId').count().sum(axis=1))
anicura_clients = anicura_clients[anicura_clients[0]>0]
anicura_clients[0] = 'Anicura'
anicura_clients = anicura_clients.reset_index()
anicura_clients.columns=['id','Anicura']
In [21]:
kinpet_clients = clients[['CustomerIdsMindboxId',
                          'CustomerCustomFieldsKinpetActivity',
                          'CustomerCustomFieldsKinpetBreedPet',
                          'CustomerCustomFieldsKinpetEmail',
                          'CustomerCustomFieldsKinpetIDrbc',
                          'CustomerCustomFieldsKinpetmobilephone',
                          'CustomerCustomFieldsKinpetMobilePhoneConfirmed',
                          'CustomerCustomFieldsKinpetNameCompany',
                          'CustomerCustomFieldsKinpetPermission',
                          'CustomerCustomFieldsKinpetPro',
                          'CustomerCustomFieldsKinpetRegisterConfirmed',
                          'CustomerCustomFieldsKinpetRegisteredFromLanding',
                          'CustomerCustomFieldsKinpetRegistrationDate',
                          'CustomerCustomFieldsKinpetSellerRole',
                          'CustomerCustomFieldsKinpetTelegram',
                          'CustomerCustomFieldsKinpetTypeClient',
                          'CustomerCustomFieldsKinpetTypeGroupCustomer',
                          'CustomerIdsKinpetContactID',
                          'CustomerIdsKinpetID',
                          'CustomerCustomerSubscriptionsKinpetIsSubscribed',
                          'CustomerCustomerSubscriptionsKinpetSmsIsSubscribed',
                          'CustomerCustomerSubscriptionsKinpetEmailIsSubscribed',
                          'CustomerCustomerSubscriptionsKinpetViberIsSubscribed',
                          'CustomerCustomerSubscriptionsKinpetMobilePushIsSubscribed',
                          'CustomerCustomerSubscriptionsKinpetWebPushIsSubscribed']]

kinpet_clients = pd.DataFrame(kinpet_clients.groupby('CustomerIdsMindboxId').count().sum(axis=1))
kinpet_clients = kinpet_clients[kinpet_clients[0]>0]
kinpet_clients[0] = 'Kinpet'
kinpet_clients = kinpet_clients.reset_index()
kinpet_clients.columns=['id','Kinpet']
In [22]:
lubimchik_clients = clients[['CustomerIdsMindboxId',
                             'CustomerCustomFieldsCITYLubimchik',
                             'CustomerCustomFieldsLubimchikAccountActivity',
                             'CustomerCustomFieldsLubimchikEmail',
                             'CustomerCustomFieldsLubimchikMobilephone',
                             'CustomerCustomFieldsLubimchikPermission',
                             'CustomerCustomFieldsLubimchikPL',
                             'CustomerCustomFieldsLubimchikRegistrationDate',
                             'CustomerCustomFieldsLubimchikTelegram',
                             'CustomerCustomFieldsRecentlyLubimchik',
                             'CustomerIdsLubimchikID',
                             'CustomerIdsLubimchikSailplay',
                             'CustomerCustomerSubscriptionsLubimchikIsSubscribed',
                             'CustomerCustomerSubscriptionsLubimchikSmsIsSubscribed',
                             'CustomerCustomerSubscriptionsLubimchikEmailIsSubscribed',
                             'CustomerCustomerSubscriptionsLubimchikViberIsSubscribed',
                             'CustomerCustomerSubscriptionsLubimchikMobilePushIsSubscribed',
                             'CustomerCustomerSubscriptionsLubimchikWebPushIsSubscribed']]

lubimchik_clients = pd.DataFrame(lubimchik_clients.groupby('CustomerIdsMindboxId').count().sum(axis=1))
lubimchik_clients = lubimchik_clients[lubimchik_clients[0]>0]
lubimchik_clients[0] = 'Lubimchik'
lubimchik_clients = lubimchik_clients.reset_index()
lubimchik_clients.columns=['id','Lubimchik']
In [23]:
petnutrition_clients = clients[['CustomerIdsMindboxId',
                                'CustomerCustomFieldsPetNutritionActivityStatus',
                                'CustomerCustomFieldsPetNutritionBrand',
                                'CustomerCustomFieldsPetNutritionCampaign1Status',
                                'CustomerCustomFieldsPetNutritionCampaign2Status',
                                'CustomerCustomFieldsPetNutritionCampaign3Status',
                                'CustomerCustomFieldsPetNutritionEmail',
                                'CustomerCustomFieldsPetNutritionEmailConfirmed',
                                'CustomerCustomFieldsPetNutritionFavoriteFood',
                                'CustomerCustomFieldsPetNutritionLastEcomSite',
                                'CustomerCustomFieldsPetNutritionLastEmailStatus',
                                'CustomerCustomFieldsPetNutritionMobilephone',
                                'CustomerCustomFieldsPetNutritionPermission',
                                'CustomerCustomFieldsPetNutritionPermissionPD',
                                'CustomerCustomFieldsPetNutritionShelter',
                                'CustomerIdsPetNutritionUserID',
                                'CustomerCustomerSubscriptionsPetNutritionIsSubscribed',
                                'CustomerCustomerSubscriptionsPetNutritionSmsIsSubscribed',
                                'CustomerCustomerSubscriptionsPetNutritionEmailIsSubscribed',
                                'CustomerCustomerSubscriptionsPetNutritionViberIsSubscribed',
                                'CustomerCustomerSubscriptionsPetNutritionMobilePushIsSubscribed',
                                'CustomerCustomerSubscriptionsPetNutritionWebPushIsSubscribed']]

petnutrition_clients = pd.DataFrame(petnutrition_clients.groupby('CustomerIdsMindboxId').count().sum(axis=1))
petnutrition_clients = petnutrition_clients[petnutrition_clients[0]>0]
petnutrition_clients[0] = 'PetNutrition'
petnutrition_clients = petnutrition_clients.reset_index()
petnutrition_clients.columns=['id','PetNutrition']
In [24]:
petstory_clients = clients[['CustomerIdsMindboxId',
                            'CustomerCustomFieldsPetStoryApproval',
                            'CustomerCustomFieldsPetStoryEmail',
                            'CustomerCustomFieldsPetStoryMobilephone',
                            'CustomerCustomFieldsPetStoryPermission',
                            'CustomerCustomFieldsPetStoryPushNotifications',
                            'CustomerIdsPetStoryOwnerID',
                            'CustomerCustomerSubscriptionsPetStoryIsSubscribed',
                            'CustomerCustomerSubscriptionsPetStorySmsIsSubscribed',
                            'CustomerCustomerSubscriptionsPetStoryEmailIsSubscribed',
                            'CustomerCustomerSubscriptionsPetStoryViberIsSubscribed',
                            'CustomerCustomerSubscriptionsPetStoryMobilePushIsSubscribed',
                            'CustomerCustomerSubscriptionsPetStoryWebPushIsSubscribed',
                            'CustomerCustomFieldsPetstoryRegistrationDate',
                            'CustomerCustomFieldsPetstoryRegistrationSource',]]

petstory_clients = pd.DataFrame(petstory_clients.groupby('CustomerIdsMindboxId').count().sum(axis=1))
petstory_clients = petstory_clients[petstory_clients[0]>0]
petstory_clients[0] = 'PetStory'
petstory_clients = petstory_clients.reset_index()
petstory_clients.columns=['id','PetStory']
In [25]:
royalcanin_clients = clients[['CustomerIdsMindboxId',
                              'CustomerCustomFieldsRoyalCaninBreederClub',
                              'CustomerCustomFieldsRoyalCaninBreedsCats',
                              'CustomerCustomFieldsRoyalCaninBreedsDogs',
                              'CustomerCustomFieldsRoyalCaninContactType',
                              'CustomerCustomFieldsRoyalCaninemail',
                              'CustomerCustomFieldsRoyalCaninEmailConfirmed',
                              'CustomerCustomFieldsRoyalCaninFiasArea',
                              'CustomerCustomFieldsRoyalCaninFiasCity',
                              'CustomerCustomFieldsRoyalCaninFiasRegion',
                              'CustomerCustomFieldsRoyalCaninIS18',
                              'CustomerCustomFieldsRoyalCaninMARSconsentdate',
                              'CustomerCustomFieldsRoyalCaninmobilephone',
                              'CustomerCustomFieldsRoyalCaninMobilePhoneConfirmed',
                              'CustomerCustomFieldsRoyalCaninPermission',
                              'CustomerCustomFieldsRoyalCaninPetOwner',
                              'CustomerCustomFieldsRoyalCaninSource',
                              'CustomerCustomFieldsRoyalCaninWorkPlace',
                              'CustomerIdsRoyalCaninCRMID',
                              'CustomerCustomerSubscriptionsRoyalCaninIsSubscribed',
                              'CustomerCustomerSubscriptionsRoyalCaninSmsIsSubscribed',
                              'CustomerCustomerSubscriptionsRoyalCaninEmailIsSubscribed',
                              'CustomerCustomerSubscriptionsRoyalCaninViberIsSubscribed',
                              'CustomerCustomerSubscriptionsRoyalCaninMobilePushIsSubscribed',
                              'CustomerCustomerSubscriptionsRoyalCaninWebPushIsSubscribed',
                              'CustomerCustomFieldsRoyalcaninRegistrationDate']]

royalcanin_clients = pd.DataFrame(royalcanin_clients.groupby('CustomerIdsMindboxId').count().sum(axis=1))
royalcanin_clients = royalcanin_clients[royalcanin_clients[0]>0]
royalcanin_clients[0] = 'RoyalCanin'
royalcanin_clients = royalcanin_clients.reset_index()
royalcanin_clients.columns=['id','RoyalCanin']
In [26]:
royalcanin_clients = clients[['CustomerIdsMindboxId',
                              'CustomerCustomFieldsRoyalCaninBreederClub',
                              'CustomerCustomFieldsRoyalCaninBreedsCats',
                              'CustomerCustomFieldsRoyalCaninBreedsDogs',
                              'CustomerCustomFieldsRoyalCaninContactType',
                              'CustomerCustomFieldsRoyalCaninemail',
                              'CustomerCustomFieldsRoyalCaninEmailConfirmed',
                              'CustomerCustomFieldsRoyalCaninFiasArea',
                              'CustomerCustomFieldsRoyalCaninFiasCity',
                              'CustomerCustomFieldsRoyalCaninFiasRegion',
                              'CustomerCustomFieldsRoyalCaninIS18',
                              'CustomerCustomFieldsRoyalCaninMARSconsentdate',
                              'CustomerCustomFieldsRoyalCaninmobilephone',
                              'CustomerCustomFieldsRoyalCaninMobilePhoneConfirmed',
                              'CustomerCustomFieldsRoyalCaninPermission',
                              'CustomerCustomFieldsRoyalCaninPetOwner',
                              'CustomerCustomFieldsRoyalCaninSource',
                              'CustomerCustomFieldsRoyalCaninWorkPlace',
                              'CustomerIdsRoyalCaninCRMID',
                              'CustomerCustomerSubscriptionsRoyalCaninIsSubscribed',
                              'CustomerCustomerSubscriptionsRoyalCaninSmsIsSubscribed',
                              'CustomerCustomerSubscriptionsRoyalCaninEmailIsSubscribed',
                              'CustomerCustomerSubscriptionsRoyalCaninViberIsSubscribed',
                              'CustomerCustomerSubscriptionsRoyalCaninMobilePushIsSubscribed',
                              'CustomerCustomerSubscriptionsRoyalCaninWebPushIsSubscribed',
                              'CustomerCustomFieldsRoyalcaninRegistrationDate']]

royalcanin_clients = pd.DataFrame(royalcanin_clients.groupby('CustomerIdsMindboxId').count().sum(axis=1))
royalcanin_clients = royalcanin_clients[royalcanin_clients[0]>0]
royalcanin_clients[0] = 'RoyalCanin'
royalcanin_clients = royalcanin_clients.reset_index()
royalcanin_clients.columns=['id','RoyalCanin']
In [45]:
brands_clients = anicura_clients.merge(
    kinpet_clients, on='id', how='outer').merge(
    lubimchik_clients, on='id', how='outer').merge(
    petnutrition_clients, on='id', how='outer').merge(
    petstory_clients, on='id', how='outer').merge(
    royalcanin_clients, on='id', how='outer')
In [46]:
brands_amount_by_clients = pd.DataFrame(brands_clients.groupby('id').count().sum(axis=1)).reset_index()
brands_amount_by_clients.columns = ['id','brands_amount']
In [47]:
brands_clients = brands_clients.merge(brands_amount_by_clients, on=['id'])
In [48]:
brands_amount_contribution = brands_amount_by_clients.groupby('brands_amount').agg({'id':'nunique'})
brands_amount_contribution['%'] = brands_amount_contribution['id']/brands_amount_contribution['id'].sum()*100
brands_amount_contribution = brands_amount_contribution.reset_index()
In [49]:
active = df_orders[df_orders['timestamp'] >= '2023-05-01'].groupby('event').agg({'user_id':'nunique'})
In [50]:
active = active.reset_index()
active.columns=['brand','6-Month Active Customers']

In [51]:
import plotly.graph_objects as go

labels = ['1','2','>3']
values = [1078644, 26141, 991]

fig = go.Figure(data=[go.Pie(labels=labels, values=values,
                             marker=dict(colors=['#666','#e2001a','#FFC069']),
                             textfont_size=15,
                             pull=[0, 0, 0.2],
                             hoverinfo='label+value'
                            ) 
                     ])
fig.update_layout(legend=dict(
    orientation="h",
    yanchor="bottom",
    y=1.02,
    xanchor="right",
    x=1
))

fig.update_layout(title='Customer Distribution by Number of Brands<br>[without RoyalCanin]',
                  font=dict(size=12, color="black"))
fig.show("notebook")
In [52]:
brands_clients = brands_clients.merge(contacts, on='id', how='left')
In [53]:
clients_by_brands = pd.DataFrame(brands_clients.count()).reset_index()[1:7]
clients_by_brands.columns=['brand','customers']
clients_by_brands['доля от базы'] = clients_by_brands['customers'] / len(brands_clients) * 100
#clients_by_brands['доля от базы'] = round(clients_by_brands['доля от базы']).astype(str) + '%'
In [54]:
clients_by_brands_1 = pd.DataFrame(brands_clients[brands_clients['brands_amount']==1].count()).reset_index()[1:7]
clients_by_brands_1.columns=['brand','only 1']
In [56]:
clients_by_brands_with_contacts = pd.DataFrame(brands_clients[brands_clients['email'].notna()|brands_clients['phone'].notna()].count()).reset_index()[1:7]
clients_by_brands_with_contacts.columns=['brand','with contacts']
In [57]:
clients_by_brands = clients_by_brands.merge(clients_by_brands_1,on='brand').merge(clients_by_brands_with_contacts, on='brand').merge(active, on='brand', how='left')
In [58]:
clients_by_brands = clients_by_brands.sort_values(by='customers',ascending=False)

In [59]:
import plotly.graph_objects as go
import pandas as pd

df = clients_by_brands

fig = go.Figure(data=[go.Table(
    header=dict(values=['Brand','Customers','% of Total','Single-Brand Customers', "With Contacts", 'Last 6-Month Customers'],
                font_color='white',
                fill_color='#666',
                align='left'),
    cells=dict(values=[df.brand, df.customers, round(df['доля от базы'],1).astype(str)+'%', df['only 1'], df['with contacts'], df['6-Month Active Customers']],
               fill_color='white',
               align='left'))
])
fig.update_layout(title='Brand Preferences Among Customers',
                  font=dict(size=12, color="black"))
fig.show("notebook")
In [60]:
brands_clients = brands_clients.fillna('')

In [61]:
venn_viz = brands_clients.copy()

venn_viz = venn_viz.replace({'':0,
                             'Anicura':1,
                             'Kinpet':1,
                             'Lubimchik':1,
                             'PetNutrition':1,
                             'PetStory':1,
                             'RoyalCanin':1
                            })
venn_viz = venn_viz[['RoyalCanin','PetNutrition','PetStory','Lubimchik','Kinpet','Anicura']]

fig = plot_upset(
    dataframes=[venn_viz],
    legendgroups=[''],
    marker_size=10,
    sorted_x="d",
    horizontal_spacing = 0.075,
    vertical_spacing = 0.1,
    column_widths=[0.2, 0.8],
    exclude_zeros=True,
    marker_colors=['#666']
)

fig.update_layout(
    width=1300,
    height=700,
    #font_family="Jetbrains Mono",
)
fig.layout.margin.update({'t':80, 'b':10,'l':50})

fig.update_layout(title='Brand Customer Intersection Scheme',
                  font=dict(size=12, color="black"))

fig.update_traces(hovertemplate="%{y}")

fig.show("notebook")
In [62]:
venn_viz = brands_clients[brands_clients['brands_amount']==2].copy()

venn_viz = venn_viz.replace({'':0,
                             'Anicura':1,
                             'Kinpet':1,
                             'Lubimchik':1,
                             'PetNutrition':1,
                             'PetStory':1,
                             'RoyalCanin':1
                            })
venn_viz = venn_viz[['RoyalCanin','PetNutrition','PetStory','Lubimchik','Kinpet','Anicura']]

fig = plot_upset(
    dataframes=[venn_viz],
    legendgroups=[''],
    marker_size=10,
    sorted_x="d",
    horizontal_spacing = 0.075,
    vertical_spacing = 0.1,
    column_widths=[0.2, 0.8],
    exclude_zeros=True,
    marker_colors=['#666']
)

fig.update_layout(
    width=1000,
    height=700,
    #font_family="Jetbrains Mono",
)
fig.layout.margin.update({'t':80, 'b':10,'l':50})

fig.update_layout(title='Brand Customer Intersection Scheme<br>[by 2]',
                  font=dict(size=12, color="black"))

fig.update_traces(hovertemplate="%{y}")

fig.show("notebook")
In [63]:
venn_viz = brands_clients[brands_clients['brands_amount']==3].copy()

venn_viz = venn_viz.replace({'':0,
                             'Anicura':1,
                             'Kinpet':1,
                             'Lubimchik':1,
                             'PetNutrition':1,
                             'PetStory':1,
                             'RoyalCanin':1
                            })
venn_viz = venn_viz[['RoyalCanin','PetNutrition','PetStory','Lubimchik','Kinpet','Anicura']]

fig = plot_upset(
    dataframes=[venn_viz],
    legendgroups=[''],
    marker_size=10,
    sorted_x="d",
    horizontal_spacing = 0.075,
    vertical_spacing = 0.1,
    column_widths=[0.2, 0.8],
    exclude_zeros=True,
    marker_colors=['#666']
)

fig.update_layout(
    width=1000,
    height=700,
    #font_family="Jetbrains Mono",
)
fig.layout.margin.update({'t':80, 'b':10,'l':50})

fig.update_layout(title='Brand Customer Intersection Scheme<br>[by 3]',
                  font=dict(size=12, color="black"))

fig.update_traces(hovertemplate="%{y}")

fig.show("notebook")
In [64]:
clients_id = clients[['CustomerIdsMindboxId',
                      'CustomerIdsAnicuraID',
                      'CustomerIdsKinpetID',
                      'CustomerIdsCraveID',
                      'CustomerIdsLubimchikID',
                      'CustomerIdsPetNutritionUserID',
                      'CustomerIdsPetStoryOwnerID',
                      'CustomerIdsRoyalCaninCRMID']]
In [65]:
clients_id = clients_id.rename(columns = {'CustomerIdsMindboxId':'ID',
                                          'CustomerIdsAnicuraID':'AnicuraID',
                                          'CustomerIdsKinpetID':'KinpetID',
                                          'CustomerIdsCraveID':'CraveID',
                                          'CustomerIdsLubimchikID':'LubimchikID',
                                          'CustomerIdsPetNutritionUserID':'PetNutritionID',
                                          'CustomerIdsPetStoryOwnerID':'PetStoryID',
                                          'CustomerIdsRoyalCaninCRMID':'RoyalCaninID'})
In [66]:
active_users = df_orders[df_orders['timestamp'] >= '2023-05-01'][['user_id']].drop_duplicates()
active_users.columns=['id']
In [67]:
active_users['active'] = True
In [68]:
brands_clients = brands_clients.merge(active_users, on=['id'], how='left')
In [69]:
subscriptions = clients[[
                         'CustomerCustomerSubscriptionsMarsIsSubscribed',
                         'CustomerCustomerSubscriptionsMarsSmsIsSubscribed',
                         'CustomerCustomerSubscriptionsMarsEmailIsSubscribed',
                         'CustomerCustomerSubscriptionsMarsViberIsSubscribed',
                         'CustomerCustomerSubscriptionsMarsMobilePushIsSubscribed',
                         'CustomerCustomerSubscriptionsMarsWebPushIsSubscribed',
                         'CustomerCustomerSubscriptionsRoyalCaninIsSubscribed',
                         'CustomerCustomerSubscriptionsRoyalCaninSmsIsSubscribed',
                         'CustomerCustomerSubscriptionsRoyalCaninEmailIsSubscribed',
                         'CustomerCustomerSubscriptionsRoyalCaninViberIsSubscribed',
                         'CustomerCustomerSubscriptionsRoyalCaninMobilePushIsSubscribed',
                         'CustomerCustomerSubscriptionsRoyalCaninWebPushIsSubscribed',
                         'CustomerCustomerSubscriptionsPetNutritionIsSubscribed',
                         'CustomerCustomerSubscriptionsPetNutritionSmsIsSubscribed',
                         'CustomerCustomerSubscriptionsPetNutritionEmailIsSubscribed',
                         'CustomerCustomerSubscriptionsPetNutritionViberIsSubscribed',
                         'CustomerCustomerSubscriptionsPetNutritionMobilePushIsSubscribed',
                         'CustomerCustomerSubscriptionsPetNutritionWebPushIsSubscribed',
                         'CustomerCustomerSubscriptionsKinpetIsSubscribed',
                         'CustomerCustomerSubscriptionsKinpetSmsIsSubscribed',
                         'CustomerCustomerSubscriptionsKinpetEmailIsSubscribed',
                         'CustomerCustomerSubscriptionsKinpetViberIsSubscribed',
                         'CustomerCustomerSubscriptionsKinpetMobilePushIsSubscribed',
                         'CustomerCustomerSubscriptionsLubimchikIsSubscribed',
                         'CustomerCustomerSubscriptionsLubimchikSmsIsSubscribed',
                         'CustomerCustomerSubscriptionsLubimchikEmailIsSubscribed',
                         'CustomerCustomerSubscriptionsLubimchikViberIsSubscribed',
                         'CustomerCustomerSubscriptionsLubimchikMobilePushIsSubscribed',
                         'CustomerCustomerSubscriptionsLubimchikWebPushIsSubscribed',
                         'CustomerCustomerSubscriptionsPetStoryIsSubscribed',
                         'CustomerCustomerSubscriptionsPetStorySmsIsSubscribed',
                         'CustomerCustomerSubscriptionsPetStoryEmailIsSubscribed',
                         'CustomerCustomerSubscriptionsPetStoryViberIsSubscribed',
                         'CustomerCustomerSubscriptionsPetStoryMobilePushIsSubscribed',
                         'CustomerCustomerSubscriptionsPetStoryWebPushIsSubscribed',
                         'CustomerCustomerSubscriptionsAnicuraIsSubscribed',
                         'CustomerCustomerSubscriptionsAnicuraSmsIsSubscribed',
                         'CustomerCustomerSubscriptionsAnicuraEmailIsSubscribed',
                         'CustomerCustomerSubscriptionsAnicuraViberIsSubscribed',
                         'CustomerCustomerSubscriptionsAnicuraMobilePushIsSubscribed',
                         'CustomerCustomerSubscriptionsAnicuraWebPushIsSubscribed']]
In [70]:
subscriptions = subscriptions[[
    'CustomerCustomerSubscriptionsPetStorySmsIsSubscribed',
    'CustomerCustomerSubscriptionsPetStoryViberIsSubscribed',
    'CustomerCustomerSubscriptionsPetStoryMobilePushIsSubscribed',
    'CustomerCustomerSubscriptionsPetStoryEmailIsSubscribed',
    'CustomerCustomerSubscriptionsKinpetViberIsSubscribed',
    'CustomerCustomerSubscriptionsKinpetEmailIsSubscribed',
    'CustomerCustomerSubscriptionsKinpetMobilePushIsSubscribed',
    'CustomerCustomerSubscriptionsAnicuraIsSubscribed',
    'CustomerCustomerSubscriptionsRoyalCaninEmailIsSubscribed',
    'CustomerCustomerSubscriptionsLubimchikEmailIsSubscribed',
    'CustomerCustomerSubscriptionsPetNutritionEmailIsSubscribed'
]]
In [71]:
subscriptions.columns=[
    'PetStory Sms',
    'PetStory Viber',
    'PetStory MobilePush',
    'PetStory Email',
    'Kinpet Viber and Sms',
    'Kinpet Email',
    'Kinpet MobilePush',
    'Anicura All Channels',
    'RoyalCanin Email',
    'Lubimchik Email',
    'PetNutrition Email']
In [72]:
subscriptions = subscriptions.replace({True:1,False:0})
In [73]:
subscriptions = subscriptions.fillna(0).astype(int)
In [74]:
subscriptions = subscriptions.reset_index().drop('index', axis=1)
In [75]:
subscriptions.sum().to_excel('all_subscriptions.xlsx')
In [76]:
subscriptions['sum'] = subscriptions.sum(axis=1)
In [77]:
subscriptions = subscriptions[subscriptions['sum']>=2]
In [78]:
subscriptions = subscriptions.drop('sum', axis=1)

In [79]:
fig = plot_upset(
                dataframes=[subscriptions],
                legendgroups=[''],
                marker_size=10,
                sorted_x="d",
                horizontal_spacing = 0.075,
                vertical_spacing = 0.1,
                column_widths=[0.2, 0.8],
                exclude_zeros=True,
                marker_colors=['#666']
)

fig.update_layout(
    width=1900,
    height=900,
    #font_family="Jetbrains Mono",
)
fig.layout.margin.update({'t':80, 'b':10,'l':50})

fig.update_layout(title='Channels Intersection Scheme',
                  font=dict(size=12, color="black"))

fig.update_traces(hovertemplate="%{y}")

fig.show("notebook")
In [80]:
df_orders['action'] = df_orders['event'] + ' : ' + 'Заказ'
In [81]:
regs['CustomerActionActionTemplateName'] = regs['CustomerActionBrandIdsSystemName'] + ' : ' + regs['CustomerActionActionTemplateName']
In [82]:
regs = regs[~regs['CustomerActionActionTemplateName'].str.contains('агрузка историч|Загрузка клиентов для рассылки|Регистрация клиента в операции|Создание клиента|Mars', na=False)]
In [83]:
actions_1 = df_orders[['user_id','action']]
actions_1.columns=['id','action']

actions_2 = regs[['CustomerActionCustomerIdsMindboxId','CustomerActionActionTemplateName']]
actions_2.columns=['id','action']

acions = pd.concat([actions_1,actions_2])
In [84]:
two_brands = brands_clients[brands_clients['brands_amount']==2]

Events of 2-brands-intersection groups¶

  1. RoyalCanin + PetNutrition (55240 клиентов)
In [85]:
two_brands_1 = two_brands[(two_brands['RoyalCanin']!='')&(two_brands['PetNutrition']!='')]
two_brands_1 = two_brands_1[['id']].merge(acions, on='id').drop_duplicates()
two_brands_1['action'].value_counts()
Out[85]:
RoyalCanin : Заказ                                                   390
PetNutrition : Добавление питомца в список (импорт исторического)      3
Белый клык : Заказ                                                     1
Name: action, dtype: int64
  1. RoyalCanin + PetStory (24160 клиентов)
In [86]:
two_brands_2 = two_brands[(two_brands['RoyalCanin']!='')&(two_brands['PetStory']!='')]
two_brands_2 = two_brands_2[['id']].merge(acions, on='id').drop_duplicates()
two_brands_2['action'].value_counts()
Out[86]:
PetStory : Заказ                                                                     5768
RoyalCanin : Заказ                                                                    282
PetStory : Доставка заказа                                                            241
PetStory : Добавление продукта в список в операции 'PetStory.AddPetToPetList'         204
PetStory : Отмена заказа                                                              148
PetStory : Оформление заказа                                                          119
PetStory : Оплата заказа                                                              112
RoyalCanin : Добавление питомца в список (импорт исторического)                        34
PetStory : Подписка клиента в операции 'PetStory.ProductFinderEnd'                     26
PetStory : PetStory Форма подписки клиента                                             21
PetStory : Авторизовался в МП                                                          13
PetStory : PetStory Оплата страховки                                                   12
PetStory : Изменение заказа в операции PetStory.UpdateOrder                             7
PetStory : Добавление питомца в список (импорт исторического)                           3
Anicura : Оформление заказа                                                             3
Белый клык : Заказ                                                                      3
PetStory : PetStory Открыл страницу "Определите заболевание питомца по симптомам"       2
Лаборатория : Заказ                                                                     1
Name: action, dtype: int64
  1. RoyalCanin + Lubimchik (8164 клиентов)
In [87]:
two_brands_2 = two_brands[(two_brands['RoyalCanin']!='')&(two_brands['Lubimchik']!='')]
two_brands_2 = two_brands_2[['id']].merge(acions, on='id').drop_duplicates()
two_brands_2['action'].value_counts()
Out[87]:
Lubimchik : Заказ                                                  1111
Lubimchik : Оформление заказа                                       128
Lubimchik : Отмена заказа                                            73
Lubimchik : Доставка заказа                                          62
RoyalCanin : Заказ                                                   35
Lubimchik : Изменение заказа в операции Lubimchik.UpdateOrder         5
RoyalCanin : Добавление питомца в список (импорт исторического)       2
Белый клык : Заказ                                                    1
Name: action, dtype: int64
  1. RoyalCanin + Kinpet (7452 клиента)
In [88]:
two_brands_2 = two_brands[(two_brands['RoyalCanin']!='')&(two_brands['Kinpet']!='')]
two_brands_2 = two_brands_2[['id']].merge(acions, on='id').drop_duplicates()
two_brands_2['action'].value_counts()
Out[88]:
Kinpet : Заказ                                                     3707
Kinpet : Изменение заказа в операции Kinpet.UpdateOrder             123
RoyalCanin : Заказ                                                  108
Kinpet : Kinpet Авторизация на сайте                                 58
Kinpet : Оформление заказа                                           34
Kinpet : Доставка заказа                                             24
RoyalCanin : Добавление питомца в список (импорт исторического)      24
Kinpet : Kinpet Форма подписки клиента                               17
Kinpet : Отмена заказа                                                5
PetStory : Заказ                                                      1
PetStory : Оформление заказа                                          1
Name: action, dtype: int64